﻿using System;
using System.Data;
using System.Data.SqlClient;
using Entities;

namespace DAL
{
    public class DAL_NewsGroup
    {
        private const string usp_SelectNewsGroup     = "usp_SelectNewsGroup";
        private const string usp_SelectNewsGroupByID = "usp_SelectNewsGroupByID";
        private const string usp_InsertNewsGroup     = "usp_InsertNewsGroup";
        private const string usp_UpdateNewsGroup     = "usp_UpdateNewsGroup";
        private const string usp_DeleteNewsGroup     = "usp_DeleteNewsGroup";

        private const Int32  OK                      = 1;
        private const Int32  NOT_OK                  = -1;

        public DataTable Get_All_NewsGroup()
        { 
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectNewsGroup, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;            
        }

        public DataTable Get_NewsGroup_ByID(Int32 p_group_id)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectNewsGroupByID, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.BigInt;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@p_group_id";
            sqlParameter[0].Value = p_group_id;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public bool DeleteNewsGroup(Int32 p_group_id)
        {            
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_DeleteNewsGroup, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.BigInt;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Group_id";
            sqlParameter[0].Value = p_group_id;
            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }

        public bool InsertNewsGroup(NewsGroup pNewsGroup)
        {
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_InsertNewsGroup, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            
            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.NVarChar;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Group_name";
            sqlParameter[0].Value = pNewsGroup.group_name;                    

            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();                
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }

        public bool UpdateNewsGroup(NewsGroup pNewsGroup)
        {
            SqlParameter[] sqlParameter = new SqlParameter[2];
            SqlCommand sqlCommand = new SqlCommand(usp_UpdateNewsGroup, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;            

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.NVarChar;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Group_name";
            sqlParameter[0].Value = pNewsGroup.group_name;

            sqlParameter[1] = new SqlParameter();
            sqlParameter[1].SqlDbType = SqlDbType.BigInt;
            sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[1].ParameterName = "@Group_id";
            sqlParameter[1].Value = pNewsGroup.group_id;

            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }

    }
}
